package daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import dao_interface.RoomDao;
import db.DbConnection;
import po.*;

public class RoomDaoImpl implements RoomDao{
	/**
	 *增加宿舍信息方法
	 */
	@Override
	public void save(Room room) {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		int result = 0;
		conn= DbConnection.getConnection();
        try {
        	sql = "INSERT INTO room VALUES(?,?)";
	        ps = conn.prepareStatement(sql);
	        ps.setString(1, room.getRoom_name());
	        ps.setLong(2, room.getRoom_id());
	        result = ps.executeUpdate();//executeUpdate()方法返回int值，值为影响的行数，增删改使用。
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
	        DbConnection.close(rs, ps);
	        System.out.println("操作结果："+result);
	}

	/**
	 *删除宿舍信息方法
	 */
	@Override
	public void delete(int id) {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int result = 0;
		String sql = null;
		conn= DbConnection.getConnection();
        try {
        	sql = "delete from room where 宿舍号  = ?";
			ps = conn.prepareStatement(sql);
	        ps.setInt(1, id);
	        result = ps.executeUpdate();
        }catch(Exception e) {
        	e.printStackTrace();
        }
        DbConnection.close(rs, ps);
	}

	/**
	 *修正宿舍信息方法
	 */
	@Override
	public void update(Room room) {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		int result = 0;
		conn = DbConnection.getConnection();
        try {
        	sql = "UPDATE room SET 宿舍名 = ? where 宿舍号 = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, room.getRoom_name());
			ps.setInt(2, room.getRoom_id());
	        result = ps.executeUpdate();
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
        DbConnection.close(rs, ps);
		
	}

	/**
	 *查询单个宿舍
	 */
	@Override
	public Room get(int id) {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		Room room = null;
		conn = DbConnection.getConnection();
		try {
			sql = "SELECT * FROM room WHERE 宿舍号 = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(1);
				int room_id = rs.getInt(2);
				room = new Room(room_id, name);
				room.toString();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return room;
	}
	
	/**
	 *查询所有宿舍
	 */
	@Override
	public List<Room> findAll() {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		List<Room> roomList = new ArrayList<Room>();
		conn = DbConnection.getConnection();
		try {
			sql = "SELECT * FROM room";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(1);
				int room_id = rs.getInt(2);
				Room room = new Room();
				room.setRoom_id(room_id);
				room.setRoom_name(name);;
				roomList.add(room);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DbConnection.close(rs,ps);
		}
		return roomList;
	}

	/**
	 *通过宿舍号查询宿舍
	 */
	@Override
	public List<Room> getRoomByName(String name) {
		List<Room> list = new ArrayList<Room>();
		Connection conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int result = -1;
		try {
			String sql = "SELECT * FROM room WHERE 姓名 = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while(rs.next()) {
				int room_id = rs.getInt(2);
				Room room = new Room();
				room.setRoom_id(room_id);
				room.setRoom_name(name);
				list.add(room);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
}
